package com.zygh.policeasset.mapper;

import com.zygh.policeasset.pojo.FinalStatement;
import com.zygh.policeasset.pojo.Project;
import com.zygh.policeasset.pojo.ProjectNameType;
import com.zygh.policeasset.pojo.Section;
import java.util.List;

import org.apache.ibatis.annotations.*;

@Mapper
public interface FinalStatementMapper {
    @Select({"select count(*) from final_statement t  where app_status = 1"})
    Integer findCount();

    @Select({"<script>"+
            "select f.*,p.name as project_name,sn.section_name," +
            "ct.name,cp.company_name as companyName,af.app_status," +
            "af.approval_steps_id as approvalStepsId,u.name as userName " +
            "from final_statement f " +
            "left join contract ct on f.section_id = ct.section_id " +
            "left join company cp on ct.company_id = cp.id " +
            "left join section sn on f.section_id = sn.id " +
            "left join project p on f.project_no = p.project_no " +
            "left join settlement s on f.project_no = s.project_no and f.section_id=s.section_id " +
            "left join audit_flow af on f.flow_no = af.flow_no " +
            "left join user u on af.applicant = u.id " +
            "<where>" +
            "<if test='name != null and name.length &gt; 0'>and p.name like '%${name}%' </if>" +
            "<if test='category != null and category &gt; 0'>and p.category=#{category} </if>" +
            "<if test='companyId != null and companyId != 0 '>and cp.id = #{companyId} </if>" +
            "<if test='userId != null '> and af.applicant=#{userId} </if>" +
            "<if test=' status != null'> and af.app_status = #{status} </if>" +
            "<if test='time1 != null and time1.length &gt; 0 and time2 != null and time2.length &gt; 0'>and f.audit_time between  #{time1} and #{time2} </if>" +
            "</where>" +
            "order by f.id desc " +
            "</script>"})
    @Results(id = "map",value = {@Result(property = "projectNo", column = "project_no"),
            @Result(property = "projectName", column = "project_name"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "assetNewStatus", column = "asset_new_status"),
            @Result(property = "sectionId", column = "section_id"),
            @Result(property = "sectionName", column = "section_name"),
            @Result(property = "contractName", column = "name"),
            @Result(property = "settlementAmount", column = "settlement_amount"),
            @Result(property = "finalStatementAmount", column = "final_statement_amount"),
            @Result(property = "increaseOrDecrease", column = "increase_or_decrease"),
            @Result(property = "auditUnit", column = "audit_unit"),
            @Result(property = "sectionName", column = "section_name"),
            @Result(property = "contractName", column = "name"),
            @Result(property = "flowNo", column = "flow_no"),
            @Result(property = "appStatus", column = "app_status"),
            @Result(property = "auditTime", column = "audit_time")})
    List<FinalStatement> findAll(@Param("name") String str, @Param("status") Integer num,
                                 @Param("companyId") Integer num2, @Param("time1") String str2,
                                 @Param("time2") String str3, @Param("category") Integer num3,
                                 @Param("userId") Integer num4);
    @Select({"<script>"+
            "select f.*,p.name as project_name,sn.section_name,ct.name," +
            "af.approval_steps_id as approvalStepsId,af.app_status,u.name as userName, " +
            "cp.company_name as companyName,sn.section_name from final_statement f " +
            "left join contract ct on f.section_id = ct.section_id " +
            "left join company cp on ct.company_id = cp.id " +
            "left join section sn on f.section_id = sn.id " +
            "left join project p on f.project_no = p.project_no " +
            "left join settlement s on f.project_no = s.project_no and f.section_id=s.section_id " +
            "left join audit_flow af on f.flow_no = af.flow_no " +
            "left join user u on af.applicant = u.id " +
            "<where>" +
            "<if test='id != null '>and f.id = #{id} </if>" +
            "</where>" +
            "</script>"})
    @ResultMap("map")
    FinalStatement findById(@Param("id") Integer num);

    @Select({"select f.*,p.name as project_name,sn.section_name,ct.name,af.app_status from final_statement f left join contract ct on f.section_id = ct.section_id left join section sn on f.section_id = sn.id left join project p on f.project_no = p.project_no left join audit_flow af on f.flow_no = af.flow_no where f.project_no=#{no} order by f.id desc "})
    @ResultMap("map")
    List<FinalStatement> findByProject(String str);

    @Select({"select f.*,p.name as project_name,pr.status as status,pr.opinion as opinion from final_statement f left join processrecord pr on f.id = pr.project_id and pr.process_name = 'final_statement' left join project p on f.project_no = p.project_no where pr.status=#{status} order by f.id desc "})
    @ResultMap("map")
    List<FinalStatement> findByStatus(int i);

    @Select({"<script>", "select p.* from settlement s left join final_statement fs on s.section_id = fs.section_id left join project p on s.project_no = p.project_no left join contract c on s.section_id = c.section_id left join company cp on c.company_id = cp.id left join audit_flow af on s.flow_no = af.flow_no where af.app_status = 2 and fs.section_id is null <if test='userId != null '>and af.applicant = #{userId} </if>GROUP BY p.project_no order by s.id desc </script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime")})
    List<Project> getProject(@Param("userId") Integer num);

    @Select({"<script>", "select sn.*,p.name as project_name,s.audit_amount from settlement s " +
            "left join final_statement fs on s.section_id = fs.section_id " +
            "left join section sn on s.section_id = sn.id " +
            "left join project p on sn.project_no = p.project_no " +
            "left join contract c on s.section_id = c.section_id " +
            "left join company cp on c.company_id = cp.id " +
            "left join audit_flow af on s.flow_no = af.flow_no " +
            "where af.app_status = 2 and fs.section_id is null and s.project_no = #{projectNo} " +
            "<if test='userId != null '>and af.applicant = #{userId} </if>" +
            "order by sn.id desc " +
            "</script>"})
    @Results({@Result(property = "projectNo", column = "project_no"),
            @Result(property = "projectName", column = "project_name"),
            @Result(property = "tenderId", column = "tender_id"),
            @Result(property = "sectionName", column = "section_name"),
            @Result(property = "sectionTime", column = "section_time"),
            @Result(property = "assectStatus", column = "assect_status"),
            @Result(property = "auditAmount", column = "audit_amount")})
    List<Section> getAdd(@Param("projectNo") String str, @Param("userId") Integer num);

    @Insert({"insert into final_statement (project_no,content,create_time,asset_new_status,section_id," +
            "settlement_amount,final_statement_amount,increase_or_decrease,amount,audit_unit,flow_no,audit_time) " +
            "values (#{projectNo},#{content},#{createTime},#{assetNewStatus},#{sectionId}," +
            "#{settlementAmount},#{finalStatementAmount},#{increaseOrDecrease},#{amount}," +
            "#{auditUnit},#{flowNo},#{auditTime})"})
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    Integer add(FinalStatement finalStatement);

    @Update({"update final_statement set project_no=#{projectNo},content=#{content},create_time=#{createTime}," +
            "asset_new_status=#{assetNewStatus},section_id=#{sectionId},settlement_amount=#{settlementAmount}," +
            "final_statement_amount=#{finalStatementAmount},increase_or_decrease=#{increaseOrDecrease}," +
            "amount=#{amount},audit_unit=#{auditUnit},audit_time=#{auditTime} where id=#{id}"})
    Integer update(FinalStatement finalStatement);

    @Delete({"delete from final_statement where id=#{id}"})
    Integer remove(int i);

    @Update({"update final_statement set asset_new_status=#{assetNewStatus} where project_no = #{projectNo} and section_id = #{sectionId}"})
    int updateStatusAssets(@Param("assetNewStatus") String str, @Param("projectNo") String str2, @Param("sectionId") Integer num);

    @Update({"update final_statement set app_status=#{status} where id=#{id}"})
    Integer updateStatus(int i, int i2);

    @Select({"SELECT DISTINCT p.name,p.project_no  FROM final_statement c " +
            "LEFT JOIN  project p ON c.project_no= p.project_no " +
            "left join audit_flow a on c.flow_no=a.flow_no WHERE a.app_status = 2 "})
    @Results({@Result(property = "projectNo", column = "project_no"),
            @Result(property = "projectName", column = "name")})
    List<ProjectNameType> selectBytype();

    @Select({"SELECT s.section_name  FROM final_statement c " +
            "LEFT JOIN  section s ON c.section_id= s.id " +
            "WHERE c.project_no = #{projectNo} "})
    @Results({@Result(property = "sectionName", column = "section_name")})
    List<ProjectNameType> findSectionByType(@Param("projectNo") String str);

    @Select({"<script>SELECT t.*,p.name as project_name,se.`section_name`,co.company_name FROM final_statement t " +
            "LEFT JOIN  project p ON t.project_no= p.project_no LEFT JOIN category c ON p.category = c.id  " +
            "LEFT JOIN `section` se ON  t.`section_id`=se.`id` " +
            "eft join settlement s on t.project_no = s.project_no and t.section_id=s.section_id " +
            "LEFT JOIN `contract` cs ON  cs.`project_no`=t.`project_no` and  cs.`section_id`=t.`section_id` " +
            "LEFT JOIN `company` co ON  cs.`company_id`=co.`id` " +
            "left join audit_flow af on t.flow_no = af.flow_no " +
            "<where>af.app_status = 2 and p.category=#{id} " +
            "<if test='name != null and name.length &gt; 0'>and p.name like '%${name}%' </if>" +
            "<if test='companyId != null and companyId != 0'>and cs.company_id = #{companyId} </if>" +
            "<if test='time1 != null and time2 != null and time1.length &gt; 0 and time2.length &gt; 0'>and s.audit_time between #{time1} and  #{time2} </if>" +
            "</where>" +
            "</script>"})
    @ResultMap("map")

    List<FinalStatement> toExcel(@Param("id") Integer id,
                                 @Param("name")String name,
                                 @Param("companyId") String companyId,
                                 @Param("time1") String time1,
                                 @Param("time2")  String time2);
}